package cn.com.codes.otherMission.blh;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.opensymphony.webwork.ServletActionContext;

import cn.com.codes.bugManager.blh.BugFlowConst;
import cn.com.codes.common.SecurityContextHolderHelp;
import cn.com.codes.common.dto.PageModel;
import cn.com.codes.common.util.FileInfoVo;
import cn.com.codes.common.util.StringUtils;
import cn.com.codes.fileInfo.dto.FileInfoDto;
import cn.com.codes.fileInfo.service.FileInfoService;
import cn.com.codes.framework.app.blh.BusinessBlh;
import cn.com.codes.framework.app.view.UniversalView;
import cn.com.codes.framework.app.view.View;
import cn.com.codes.framework.common.ConvertObjArrayToVo;
import cn.com.codes.framework.common.JsonUtil;
import cn.com.codes.framework.security.VisitUser;
import cn.com.codes.framework.security.filter.SecurityContextHolder;
import cn.com.codes.framework.transmission.JsonInterface;
import cn.com.codes.framework.transmission.events.BusiRequestEvent;
import cn.com.codes.iteration.dto.IterationVo;
import cn.com.codes.object.ConcernOtherMission;
import cn.com.codes.object.FileInfo;
import cn.com.codes.object.IterationTaskReal;
import cn.com.codes.object.MissionLog;
import cn.com.codes.object.OtherMission;
import cn.com.codes.object.SingleTestTask;
import cn.com.codes.object.User;
import cn.com.codes.object.UserOtherMission;
import cn.com.codes.otherMission.dto.OtherMissionDto;
import cn.com.codes.otherMission.service.OtherMissionService;
import net.sf.json.JSONArray;
public class OtherMissionBlh extends BusinessBlh {

	private OtherMissionService otherMissionService;
	private FileInfoService fileInfoService;
	private static Logger logger = Logger.getLogger(OtherMissionBlh.class);
	
	/** 
	* 方法名:          otherMissionList
	* 方法功能描述:    跳转到其他任务页面
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月25日 下午4:02:32
	*/
	public View otherMissionList(BusiRequestEvent req){
		return super.getView();
	}
	
	/** 
	* 方法名:          toMeCharge
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月3日 上午10:59:12
	*/
	public View toMeCharge(BusiRequestEvent req){
		return super.getView();
	}
	
	/** 
	* 方法名:          toMeJoin
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月3日 上午10:59:16
	*/
	public View toMeJoin(BusiRequestEvent req){
		return super.getView();
	}
	
	/** 
	* 方法名:          toMeConcern
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月12日 上午10:52:19
	*/
	public View toMeConcern(BusiRequestEvent req){
		return super.getView();
	}
	
	/** 
	* 方法名:          allMissions
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月12日 上午10:52:54
	*/
	public View allMissions(BusiRequestEvent req){
		return super.getView();
	}
	
	/** 
	* 方法名:          overview
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   
	*/
	public View overview(BusiRequestEvent req){
		return super.getView();
	}
	/** 
	* 方法名:          otherMissionListLoad
	* 方法功能描述:    获取其他任务列表
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月25日 下午4:03:14
	*/
	@SuppressWarnings("unchecked")
	public View otherMissionListLoad(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		OtherMission otherMission = dto.getOtherMission();
		if(otherMission!=null&&otherMission.getMissionName()!=null&&"%".equals(otherMission.getMissionName().trim())){
			PageModel pageModel = new PageModel(); 
			pageModel.setRows(new ArrayList<IterationVo>());
			pageModel.setTotal(0);
			super.writeResult(JsonUtil.toJson(pageModel));
			return super.globalAjax();
		}
		this.buildOtherMissionListSql(dto);
		PageModel pg = new PageModel();
		pg.setPageNo(dto.getPageNo());
		pg.setPageSize(dto.getPageSize());
		pg.setHqlParamMap(dto.getHqlParamMaps());
		pg.setQueryHql(dto.getHql());
		otherMissionService.getJdbcTemplateWrapper().fillPageModelData(pg, OtherMission.class, "");
		setMissionJoiners((List<OtherMission>)pg.getRows());
		//pg.setRows(otherMissions);
		//pg.setTotal(dto.getTotal());
		
		super.writeResult(JsonUtil.toJson(pg));
		return super.globalAjax();

	}
	
	private void buildOtherMissionListSql(OtherMissionDto dto){
		StringBuffer hql = new StringBuffer();
		StringBuffer hql2 = new StringBuffer();
		OtherMission otherMission = dto.getOtherMission();
		hql.append("SELECT omn.* FROM t_other_mission omn    join t_single_test_task st on omn.project_id= st.taskid   and  st.status_flg<>4  ");
		hql2.append("SELECT omn.* FROM t_other_mission omn  where omn.project_id is null or omn.project_id ='' ");
		Map<String,Object> praValMap = new HashMap<String,Object>();
		if(!StringUtils.isNullOrEmpty(dto.getOtherChargeId())){//
			hql2.append("  and omn.charge_person_id=:otherChargeId ");
			hql.append("  and omn.charge_person_id=:otherChargeId  ");
			praValMap.put("otherChargeId", dto.getOtherChargeId());
			
		}
		
		if(dto.getStartTime() != null){
			hql2.append("  and ifnull(omn.real_start_time, omn.predict_start_time) > :startTime ");
			hql.append("  and ifnull(omn.real_start_time, omn.predict_start_time) > :startTime ");
			praValMap.put("startTime", dto.getStartTime());
		}
		
		//根据任务参与者查询
		if(dto.getUserId()!=null&&!"".equals(dto.getUserId())){
			String hql1 = "from UserOtherMission uom where uom.userId=?";
			List<UserOtherMission> userOtherMissions = otherMissionService.findByHql(hql1, dto.getUserId());
			if(userOtherMissions != null && userOtherMissions.size() > 0){
				List<String> missionIds = new ArrayList<String>();
				for(int i=0;i<userOtherMissions.size();i++){
					missionIds.add(userOtherMissions.get(i).getMissionId());
				}
				hql.append("  and  omn.mission_id in (:missionIds) ");
				hql2.append(" and  omn.mission_id in (:missionIds) ");
				praValMap.put("missionIds", missionIds);
				hql.append("  and  omn.charge_person_id !=:chargePersonId ");
				hql2.append(" and  omn.charge_person_id !=:chargePersonId  ");
				praValMap.put("chargePersonId", dto.getUserId());
			}else{
				List<String> missionIds = new ArrayList<String>();
				missionIds.add("123");
				hql.append("  and  omn.mission_id in (:missionIds) ");
				hql2.append("  and  omn.mission_id in (:missionIds)   ");
				praValMap.put("missionIds", missionIds);
				hql.append("  and  omn.charge_person_id !=:chargePersonId ");
				hql2.append(" and  omn.charge_person_id !=:chargePersonId  ");
				praValMap.put("chargePersonId", dto.getUserId());
			}
		}
		//根据任务关注者查询任务
		if(dto.getConcernId()!=null&&!"".equals(dto.getConcernId())){
			String hql1 = "from ConcernOtherMission com where com.userId=?";
			List<ConcernOtherMission> concernOtherMissions = otherMissionService.findByHql(hql1, dto.getConcernId());
			if(concernOtherMissions != null && concernOtherMissions.size() > 0){
				List<String> missionIds = new ArrayList<String>();
				for(int i=0;i<concernOtherMissions.size();i++){
					missionIds.add(concernOtherMissions.get(i).getMissionId());
				}
				hql.append("  and  omn.mission_id in (:missionIdss) ");
				hql2.append("  and  omn.mission_id in (:missionIdss) ");
				praValMap.put("missionIdss", missionIds);
			}else{
				List<String> missionIds = new ArrayList<String>();
				missionIds.add("123");
				hql.append("  and  omn.mission_id in (:missionIds) ");
				hql2.append("  and  omn.mission_id in (:missionIds) ");
				praValMap.put("missionIds", missionIds);
			}
		}
		
		if(otherMission!=null){
			//根据任务名称查询
			if(otherMission.getMissionName()!=null&&!"".equals(otherMission.getMissionName())){
				hql.append("  and  omn.mission_name like :missionName ");
				hql2.append("  and  omn.mission_name like :missionName ");
				praValMap.put("missionName", "%"+otherMission.getMissionName()+"%");
			}
			//根据任务负责人查询
			if(otherMission.getChargePersonId()!=null&&!"".equals(otherMission.getChargePersonId())){
				hql.append("  and  omn.charge_person_id =:chargePersonId ");
				hql2.append("  and  omn.charge_person_id =:chargePersonId ");
				praValMap.put("chargePersonId", otherMission.getChargePersonId());
			}
			//根据任务创建者查询
			if(otherMission.getCreateUserId()!=null&&!"".equals(otherMission.getCreateUserId())){
				hql.append("  and  omn.create_user_id =:createUserId ");
				hql2.append("  and  omn.create_user_id =:createUserId ");
				praValMap.put("createUserId", otherMission.getCreateUserId());
			}
			//根据任务状态查询
			if(otherMission.getStatus()!=null&&!"".equals(otherMission.getStatus())&&!"5".equals(otherMission.getStatus())){
				hql.append("  and  omn.status =:status ");
				hql2.append("  and  omn.status =:status ");
				praValMap.put("status", otherMission.getStatus());
			}
			//根据所属项目查询
			if(otherMission.getProjectId()!=null&&!"".equals(otherMission.getProjectId())){
				hql.append("  and  omn.project_id =:projectId ");
				hql2.append("  and  omn.project_id =:projectId ");
				praValMap.put("projectId", otherMission.getProjectId());
			}
			
			if("5".equals(otherMission.getStatus())){
				hql.append("  and  (omn.status ='1' or omn.status ='0') and omn.predict_end_time<=now() ");
				hql2.append("  and  (omn.status ='1' or omn.status ='0')  and  omn.predict_end_time<=now() ");
			}
		}

		
		if(!StringUtils.isNullOrEmpty(dto.getRelaMissionId())){
			List<String> relaMissions = new ArrayList<String>();
			String[] relaMissionId = dto.getRelaMissionId().split(" ");
			for(int i=0;i<relaMissionId.length;i++){
				relaMissions.add(relaMissionId[i]);
			}
			
			hql.append(" and omn.mission_id not in (:relaMissionIds) ");
			hql2.append(" and omn.mission_id not in (:relaMissionIds) ");
			praValMap.put("relaMissionIds", relaMissions);
		}
		
		
		if(dto.getRelaMissionId() != null){
			List<String> relaMissions1 = new ArrayList<String>();
			String hql1 = "from IterationTaskReal it where 1=1 and it.iterationMissionId is not null";
			List<IterationTaskReal> iterationTaskReals = otherMissionService.findByHql(hql1);
			if(iterationTaskReals != null && iterationTaskReals.size() > 0){
				for(int i=0;i<iterationTaskReals.size();i++){
					relaMissions1.add(iterationTaskReals.get(i).getMissionId());
				}
				hql.append(" and omn.mission_id not in (:relaMissionIds1) ");
				hql2.append(" and omn.mission_id not in (:relaMissionIds1) ");
				praValMap.put("relaMissionIds1", relaMissions1);
			}
		}
		String sql = "select  base.* from ("+hql.toString()+
				     "  union all " + hql2.toString() +") base order by base.update_time desc";
		dto.setHql(sql);
		if(logger.isInfoEnabled()){
			logger.info(sql.toString());
		}
		dto.setHqlParamMaps(praValMap);
	}
	/** 
	* 方法名:          add
	* 方法功能描述:    增加其他任务
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月25日 下午4:18:51
	*/
	@SuppressWarnings("unchecked")
	public View add(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		List<FileInfoVo> fileInfos = dto.getFileInfos();
		List<FileInfoVo> filesInfo = null;
		if(fileInfos!=null && fileInfos.size()>0){
			JSONArray json = JSONArray.fromObject(fileInfos.toString().replace("[[", "[").replace("]]", "]]"));
			filesInfo= (List<FileInfoVo>)JSONArray.toCollection(json, FileInfoVo.class);
			dto.setFileInfos(filesInfo);
		}
		//保存任务编号
		OtherMissionDto otherMissionDto = new OtherMissionDto();
		otherMissionDto.setPageNo(1);
		otherMissionDto.setPageSize(1);
		this.buildOtherMissionListSql(otherMissionDto);
		PageModel pg = new PageModel();
		pg.setPageNo(1);
		pg.setPageSize(1);
		pg.setHqlParamMap(otherMissionDto.getHqlParamMaps());
		pg.setQueryHql(otherMissionDto.getHql());
		otherMissionService.getJdbcTemplateWrapper().fillPageModelData(pg, OtherMission.class, "");
		List<OtherMission> otherMissions = (List<OtherMission>)pg.getRows();
		if(otherMissions != null && otherMissions.size() > 0){
			if(!StringUtils.isNullOrEmpty(otherMissions.get(0).getMissionNum())){
				int number = Integer.parseInt(otherMissions.get(0).getMissionNum()) + 1;
				dto.getOtherMission().setMissionNum(String.valueOf(number));
			}else{
				dto.getOtherMission().setMissionNum("1");
			}
		}else{
			dto.getOtherMission().setMissionNum("1");
		}
		String flag = otherMissionService.addOtherMission(dto);
		if(flag.equals("existed")){
			super.writeResult("existed");
		}else{
			super.writeResult("success");
		}
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          update
	* 方法功能描述:    更新其他任务
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月25日 下午4:20:19
	*/
	public View update(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String flag = otherMissionService.updateOtherMission(dto);
		if(flag.equals("existed")){
			super.writeResult("existed");
		}else{
			super.writeResult("success");
		}
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          delete
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月27日 下午1:43:14
	*/
	public View delete(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		otherMissionService.deleteOtherMission(dto);
		super.writeResult("success");
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          getUsers
	* 方法功能描述:    查出mission所对应的userIds
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月27日 下午1:43:41
	*/
	public View getUsers(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String[] userIds  = otherMissionService.getUsers(dto);
		String ids = "";
		if(userIds != null){
			for(int i=0;i<userIds.length;i++){
				if(i != userIds.length-1){
					ids = ids + userIds[i] + ",";
				}else{
					ids = ids + userIds[i];
				}
			}
		}
		super.writeResult(ids);
		return super.globalAjax();
	}	
	
	/** 
	* 方法名:          getConcerns
	* 方法功能描述:    查出mission所对应的userIds（关注者）
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月12日 上午10:12:54
	*/
	public View getConcerns(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String[] userIds  = otherMissionService.getConcerns(dto);
		String ids = "";
		if(userIds != null){
			for(int i=0;i<userIds.length;i++){
				if(i != userIds.length-1){
					ids = ids + userIds[i] + ",";
				}else{
					ids = ids + userIds[i];
				}
			}
		}
		super.writeResult(ids);
		return super.globalAjax();
	}	


	/** 
	* 方法名:          addProject
	* 方法功能描述:    新增项目
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月28日 上午10:13:31
	*/
	public View addProject(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		Boolean status = otherMissionService.addProject(dto);
		if(status){
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("oprateResult", "success");
			map.put("project", dto.getProject());
			super.writeResult(JsonUtil.toJson(map));
		}else{
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("oprateResult", "failed");
			super.writeResult(JsonUtil.toJson(map));
		}
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          getProjectLists
	* 方法功能描述:    得到项目列表(所有)
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月28日 上午10:19:57
	*/
	public View getProjectLists(BusiRequestEvent req){

		String sql = "SELECT TASKID as projectId , PRO_NAME as projectName  FROM   t_single_test_task "+
				 " where status_flg!=4 ";

		List<Map<String,Object>> objectsList = otherMissionService.commonfindBySqlByJDBC(sql,false,new HashMap<String,Object>()); 
		PageModel pg = new PageModel();
		if(objectsList == null){
			pg.setRows(new ArrayList<Map<String,Object>>());
		}else{
			pg.setRows(objectsList);
		}
		super.writeResult(JsonUtil.toJson(pg));
		return super.globalAjax();
	}
	
	public View getProjectLists1(BusiRequestEvent req){

		return getProjectLists(req);
	}
	
	public View getProjectLists2(BusiRequestEvent req){

		return getProjectLists(req);
	}
	
	/** 
	* 方法名:          getProjectListsRelated
	* 方法功能描述:    得到项目列表(与我有关的)
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年10月19日 下午1:33:46
	*/
	public View getProjectListsRelated(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);

		String currUserId  = SecurityContextHolderHelp.getUserId();
		String loginName = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getLoginName();
		String sql = "SELECT TASKID as projectId , PRO_NAME as projectName  FROM   t_single_test_task"+
				" where STATUS_FLG <> 4 and (CREATE_ID='"+currUserId+"' or psm_id='"+currUserId+"') and status_flg!=4"+
				" union"+
				" SELECT  distinct  	pro.TASKID  as project_id , pro.pro_name as project_name  FROM t_single_test_task pro, "+
				" (SELECT om.* FROM t_other_mission om"+
				" join   t_user_other_mission uom on om.mission_id=uom.mission_id and om.project_id is not null"+
				" and uom.user_id='"+currUserId+"' "+
				" union"+
				" SELECT om.* FROM t_other_mission om"+
				" join   t_concern_other_mission com on om.mission_id=com.mission_id and om.project_id is not null"+
				" and com.user_id='"+currUserId+"' "+
				" union"+
				" SELECT om.* FROM t_other_mission om  where   om.create_user_id='"+loginName+"' "+
				" and om.project_id is not null) proTask"+
				" where pro.taskid = proTask.project_id and  pro.status_flg != 4 "+
				" union"+
				" SELECT  distinct t.taskid as project_id , t.PRO_NAME as project_name  FROM t_single_test_task t"+
				" join t_task_useactor uat on t.taskid=uat.taskid and uat.is_enable=1 and t.STATUS_FLG <> 4 "+
				" and uat.userid='"+currUserId+"' "+
				" union"+
				" SELECT  distinct  pro.taskid as projectId, pro.pro_name as projectName  FROM t_single_test_task  pro ,"+
				" (SELECT om.* FROM t_other_mission om"+
				" join   t_user_other_mission uom on om.mission_id=uom.mission_id and om.project_id is not null"+
				" and uom.user_id='"+currUserId+"' "+
				" union"+
				" SELECT om.* FROM t_other_mission om"+
				" join   t_concern_other_mission com on om.mission_id=com.mission_id and om.project_id is not null"+
				" and com.user_id='"+currUserId+"' "+
				" union"+
				" SELECT om.* FROM t_other_mission om  where   om.create_user_id='"+loginName+"' "+
				" and om.project_id is not null) proTask"+
				" where pro.taskid =proTask.project_id and pro.STATUS_FLG <> 4 ";

		List<Map<String,Object>> objectsList = otherMissionService.commonfindBySqlByJDBC(sql,false,new HashMap<String,Object>()); 
		PageModel pg = new PageModel();
		if(objectsList == null){
			pg.setRows(new ArrayList<Map<String,Object>>());
		}else{
			pg.setRows(objectsList);
		}
		pg.setTotal(dto.getTotal());
		super.writeResult(JsonUtil.toJson(pg));
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          getPeopleLists
	* 方法功能描述:    得到可执行任务的工作人员
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年6月28日 下午1:45:07
	*/
	public View getPeopleLists(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		List<User> users  = otherMissionService.getPeopleLists(dto);
		PageModel pg = new PageModel();
		if(users == null){
			pg.setRows(new ArrayList<User>());
		}else{
			pg.setRows(users);
		}
		super.writeResult(JsonUtil.toJson(pg));
		return super.globalAjax();
	}
	/** 
	* 方法名:          getUserNames
	* 方法功能描述:    得到所有任务参与者名字
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月4日 下午1:48:28
	*/
	public View getUserNames(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String[] userNames  = otherMissionService.getUserNames(dto);
		String names = "";
		if(userNames != null){
			for(int i=0;i<userNames.length;i++){
				if(i != userNames.length-1){
					names = names + userNames[i] + ",";
				}else{
					names = names + userNames[i];
				}
			}
		}
		super.writeResult(names);
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          getConcernNames
	* 方法功能描述:    得到所有任务关注者名字
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月12日 上午10:14:00
	*/
	public View getConcernNames(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String[] userNames  = otherMissionService.getConcernNames(dto);
		String names = "";
		if(userNames != null){
			for(int i=0;i<userNames.length;i++){
				if(i != userNames.length-1){
					names = names + userNames[i] + ",";
				}else{
					names = names + userNames[i];
				}
			}
		}
		super.writeResult(names);
		return super.globalAjax();
	}
	/** 
	* 方法名:          updateStatus
	* 方法功能描述:    更新任务状态，进度，实际工作量
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月3日 上午11:22:30
	*/
	public View updateStatus(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		otherMissionService.updateStatus(dto);
		super.writeResult("success");
		return super.globalAjax();
	}

	/** 
	* 方法名:          concernMissions
	* 方法功能描述:    关注项目
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年7月12日 下午1:39:38
	*/
	public View concernMissions(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		otherMissionService.concernMissions(dto);
		super.writeResult("success");
		return super.globalAjax();
	}
	
	/** 
	* 方法名:          getMissionLog
	* 方法功能描述:    得到任务日志信息
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年8月31日 上午9:41:13
	*/
	@SuppressWarnings("unchecked")
	public View getMissionLog(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		//构造hql查询语句
		this.buildOtherMissionLogListHql(dto);
		List<MissionLog> missionLogs  = otherMissionService.findByHqlWithValuesMap(dto);
		PageModel pg = new PageModel();
		pg.setPageNo(dto.getPageNo());
		pg.setPageSize(dto.getPageSize());
		pg.setRows(missionLogs);
		pg.setTotal(dto.getTotal());
		super.writeResult(JsonUtil.toJson(pg));
		return super.globalAjax();
	}
	/** 
	* 方法名:          buildOtherMissionLogListHql
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年8月31日 上午9:42:58
	*/
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private void buildOtherMissionLogListHql(OtherMissionDto dto){
		StringBuffer hql = new StringBuffer();
		OtherMission otherMission = dto.getOtherMission();
		hql.append("from MissionLog ml where 1=1 ");
		Map praValMap = new HashMap();
		if(otherMission!=null){
			//根据任务Id查询
			if(otherMission.getMissionId()!=null&&!"".equals(otherMission.getMissionId())){
				hql.append("  and  ml.missionId = :missionId ");
				praValMap.put("missionId", otherMission.getMissionId());
			}
		}
		hql.append(" order by ml.operateTime desc");
		dto.setHql(hql.toString());
		if(logger.isInfoEnabled()){
			logger.info(hql.toString());
		}
		dto.setHqlParamMaps(praValMap);
	}
	
	
	class taskListVo implements ConvertObjArrayToVo{
		public List<?> convert(List<?> resultSet){
			if(resultSet==null||resultSet.isEmpty()){
				return null;
			}
			List<JsonInterface> list = new ArrayList<JsonInterface>(resultSet.size());
			Iterator it = resultSet.iterator();
			while(it.hasNext()){
				SingleTestTask task = new SingleTestTask();
				Object values[] = (Object[])it.next();
				task.setTaskId(values[0].toString());
				task.setProNum(values[1].toString());
				task.setProName(values[2]==null?"":values[2].toString());
				task.setDevDept(values[3]==null?"":values[3].toString());
				task.setTestPhase(Integer.parseInt(values[4].toString()));
				task.setPsmName(values[5].toString()+"("+values[6].toString()+")");
				task.setPlanStartDate((Date)values[7]);
				task.setPlanEndDate((Date)values[8]);
				task.setStatus(Integer.parseInt(values[9].toString()));
				//task.setPlanDocName(values[9]==null?"":values[10].toString());
				task.setPlanDocName(values[10]==null?"":values[10].toString());
				task.setTaskType(values[11]==null?"":values[11].toString());
				task.setPsmId(values[10]==null?"":values[10].toString());
				task.setFilterFlag(values[13]==null?"":values[13].toString());
				task.setOutlineState(Integer.valueOf(values[14]==null?"":values[14].toString()));
				list.add(task);
			}
			return list;
		}
	}
	

	/** 
	* 方法名:          getOtherMissionList
	* 方法功能描述:    得到看板上的任务信息
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年12月27日 上午11:27:59
	*/
	public View getOtherMissionList(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date(); 
		date.setHours(0);
		date.setMinutes(0);
		date.setSeconds(0);
		
		Calendar cal = Calendar.getInstance(); 
		cal.setTime(date); 
		cal.add(Calendar.MONTH, -6);
		date = cal.getTime();
		String agoTime = format.format(date);
		/*管理员,查出所有任务*/
		int isAdmin = SecurityContextHolderHelp.getUserIsAdmin();
		if((isAdmin == 1 || isAdmin == 2) && StringUtils.isNullOrEmpty(dto.getUsersId())){
			String sql = "SELECT om.* FROM t_other_mission om  join t_single_test_task st on om.project_id= st.taskid  and st.status_flg<>4  where 1=1 ";
			String sql2 = "SELECT om.* FROM t_other_mission om  where (om.project_id is  null or om.project_id ='' )  ";
			String queryPkgSql = "select tp.taskId as project_id,tp.id as mission_id,tp.executor, tp.package_name as mission_name,IFNULL(tp.exe_count,0) as exe_count,IFNULL(tp.not_exe_count,0) as not_exe_count ,tp.expected_start_time,tp.expected_end_time,tp.actual_start_time, st.pro_name as projectName from t_testcasepackage  tp";
			queryPkgSql = queryPkgSql +" join t_single_test_task st on tp.taskId= st.taskid and st.status_flg<>4 ";
			String queryItrtSql = "select  itrt.iteration_id as  mission_id ,itrt.task_id as project_id ,itrt.iteration_bag_name as mission_name ,itrt.iteration_status as status ,itrt.start_time ,itrt.end_time,itrt.association_project as projectName, itrt.create_time,itrt.update_time from t_iteration_list itrt join t_single_test_task st on itrt.task_id= st.taskid  and  st.status_flg<>4 where 1=1 ";
			String queryItrtSql2 = "select  itrt.iteration_id as  mission_id ,itrt.task_id as project_id ,itrt.iteration_bag_name as mission_name ,itrt.iteration_status as status ,itrt.start_time ,itrt.end_time,itrt.association_project as projectName , itrt.create_time,itrt.update_time from t_iteration_list itrt where  (itrt.task_id is null or itrt.task_id='' )   ";
			HashMap<String, Object> map = new HashMap<String, Object>();
			if(dto.getOtherMission() != null){
				if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
					sql = sql + " and om.project_id= " + "'"+dto.getOtherMission().getProjectId()+"'";
					sql2 = sql2 + " and om.project_id= " + "'"+dto.getOtherMission().getProjectId()+"'";
					queryPkgSql = queryPkgSql + " where  tp.taskId= " + "'"+dto.getOtherMission().getProjectId()+"'";
					queryItrtSql  =  queryItrtSql + " and  itrt.task_id=" + "'"+dto.getOtherMission().getProjectId()+"'";
					queryItrtSql2 = queryItrtSql2 + " and  itrt.task_id=" + "'"+dto.getOtherMission().getProjectId()+"'";
					/*map.put("projectId", dto.getOtherMission().getProjectId());*/
				}else{
					sql = sql + " and om.create_time > "+ "'"+agoTime+"'";
					sql2 = sql2 + " and om.create_time > "+ "'"+agoTime+"'";
					queryPkgSql = queryPkgSql + " where  tp.create_time > "+ "'"+agoTime+"'";
					queryItrtSql  =  queryItrtSql + " and itrt.create_time > "+ "'"+agoTime+"'";
					queryItrtSql2  =  queryItrtSql2 + " and itrt.create_time > "+ "'"+agoTime+"'";
				}
			}
		
			String exeSql = "select  base.* from ("+sql+
				     "  union all " + sql2 +") base order by base.update_time,base.create_time desc ";
			List<Map<String,Object>> missions = otherMissionService.commonfindBySqlByJDBC(exeSql, true, map); 
			for(Map<String,Object> taskMap :missions) {
				taskMap.put("dataType", "task");
			}
			
			List<Map<String,Object>> testPkgList =  otherMissionService.commonfindBySqlByJDBC(queryPkgSql, true, map); 
			for(Map<String,Object> tpkgMap :testPkgList) {
				tpkgMap.put("dataType", "testPkg");
				int exeCount =Integer.parseInt(tpkgMap.get("exeCount").toString());
				int notExeCount =Integer.parseInt(tpkgMap.get("notExeCount").toString());
				if(exeCount>0&&notExeCount==0) {
					tpkgMap.put("status", "2");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount));
				} else if(exeCount>0&&notExeCount>0) {
					tpkgMap.put("status", "1");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount+notExeCount));
				} else if(exeCount==0&&notExeCount>0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "0/"+String.valueOf(notExeCount));
				}  else if(exeCount==0&&notExeCount==0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "无用例");
				} 
			}

			if(!testPkgList.isEmpty()) {
				missions.addAll(0,testPkgList);
			}
			String queryBugSql = null;
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				queryBugSql = "SELECT b.TASK_ID AS project_id,b.BUGCARDID AS mission_id,b.BUG_NUM  AS next_owner_id ,u.name as handler_name,b.BUGDESC  as mission_name,b.CURRENT_STATE AS bug_status,'bug' AS 'data_type'  " ;
				queryBugSql = queryBugSql +" FROM t_bugbaseinfo b join t_user  u on b.BUG_NUM = u.ID WHERE b.task_id = '"+dto.getOtherMission().getProjectId()+"' and  b.CURRENT_STATE not in(4,5,14,15,22,23) and  b.BUG_NUM  ='"+SecurityContextHolderHelp.getUserId()+"' and CURRENT_HANDLER <> '"+SecurityContextHolderHelp.getUserId()+"' ";
			}
			if(queryBugSql!=null) {
				List<Map<String,Object>> bugList =  otherMissionService.commonfindBySqlByJDBC(queryBugSql, true, map); 
				if(!bugList.isEmpty()) {
					for(Map<String,Object> bugMap :bugList) {
						bugMap.put("status", "0");
						if(bugMap.get("bugStatus")!=null) {
							int bugStatus =Integer.parseInt(bugMap.get("bugStatus").toString());
							bugMap.put("bugStatus",BugFlowConst.getStateName(bugStatus));
						}
					}
					missions.addAll(bugList);
				}
			}
			String exeItrtSql = "select  base.* from ("+queryItrtSql+
				     "  union all " + queryItrtSql2 +") base order by base.update_time,base.create_time desc";
			List<Map<String,Object>> itrtList =  otherMissionService.commonfindBySqlByJDBC(exeItrtSql, true, map); 
			for(Map<String,Object> itrtMap :itrtList) {
				itrtMap.put("dataType", "itrt");
				if(itrtMap.get("status")==null) {
					itrtMap.put("status", "0");
				}else {
				////0、进行中，1、完成，2、结束，3、准备，5、暂停，6、终止  转换为前端统一的状态 
					int intStatus =Integer.parseInt(itrtMap.get("status").toString());
					if(intStatus==0) {
						itrtMap.put("status", "1");
					} else if(intStatus==1) {
						itrtMap.put("status", "2");
					}else if(intStatus==2||intStatus==6) {
						itrtMap.put("status", "3");
					}  else if(intStatus==3) {
						itrtMap.put("status", "0");
					}else if(intStatus==5) {
						itrtMap.put("status", "4");
					}
				}
			}

			if(!itrtList.isEmpty()) {
				missions.addAll(itrtList);
			}
			if(missions != null && missions.size() > 0){
				super.writeResult(JsonUtil.toJson(missions));
			}else{
				super.writeResult(JsonUtil.toJson(new ArrayList<Map<String,Object>>()));
			}
		}else if((isAdmin == 1 || isAdmin == 2) && !StringUtils.isNullOrEmpty(dto.getUsersId())){
			User user = otherMissionService.get(User.class, dto.getUsersId());
			String sql = "select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id join t_single_test_task st on om.project_id= st.taskid and   st.status_flg<>4 where 1=1 "+
					" and uom.user_id="+ "'"+dto.getUsersId()+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id join t_single_test_task st on om.project_id= st.taskid  and st.status_flg<>4 where 1=1  "+
					" and com.user_id="+ "'"+dto.getUsersId()+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om join t_single_test_task st on om.project_id= st.taskid and  st.status_flg<>4  where om.create_user_id="+ "'"+user.getLoginName()+"'"+
					" ) misson where misson.mission_id is not null ";
			String sq2 = "select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id where  (om.project_id is null or om.project_id='') "+
					" and uom.user_id="+ "'"+dto.getUsersId()+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id where  (om.project_id is null or om.project_id='') "+
					" and com.user_id="+ "'"+dto.getUsersId()+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om where  (om.project_id is null or om.project_id='') and  om.create_user_id="+ "'"+user.getLoginName()+"'"+
					" ) misson where misson.mission_id is not null ";
			String queryPkgSql = "SELECT tp.taskId as project_id, tp.id as mission_id,tp.executor, tp.package_name  as mission_name,IFNULL(tp.exe_count,0) as exe_count,IFNULL(tp.not_exe_count,0) as not_exe_count ,tp.expected_start_time,tp.expected_end_time,"+
			" tp.actual_start_time, st.pro_name as projectName FROM t_testcasepackage tp join   t_user_testcasepkg utpgk "+
			" on tp.id = utpgk.packageId  and utpgk.userId='"+dto.getUsersId()+"'  join t_single_test_task st on tp.taskId= st.taskid and st.status_flg!=4 " ;
			
			String queryItrtSql = "select  itrt.iteration_id as  mission_id ,itrt.task_id as project_id ,itrt.iteration_bag_name as mission_name ,itrt.iteration_status as status ,itrt.start_time ,itrt.end_time,itrt.association_project as projectName , itrt.create_time,itrt.update_time from t_iteration_list itrt  join t_single_test_task st on itrt.task_id= st.taskid and   st.status_flg<>4 where itrt.user_id='"+dto.getUsersId()+"' ";
			String queryItrtSql2 = "select  itrt.iteration_id as  mission_id ,itrt.task_id as project_id ,itrt.iteration_bag_name as mission_name ,itrt.iteration_status as status ,itrt.start_time ,itrt.end_time,itrt.association_project as projectName , itrt.create_time,itrt.update_time from t_iteration_list itrt where  (itrt.task_id is null or itrt.task_id='' )   ";
			String queryBugSql = null;
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				queryBugSql = "SELECT b.TASK_ID AS project_id,b.BUGCARDID AS mission_id,b.BUG_NUM  AS next_owner_id ,u.name as handler_name,b.BUGDESC  as mission_name,b.CURRENT_STATE AS bug_status,'bug' AS 'data_type'  " ;
				queryBugSql = queryBugSql +" FROM t_bugbaseinfo b join t_user  u on b.BUG_NUM = u.ID WHERE b.task_id = '"+dto.getOtherMission().getProjectId()+"' and  b.CURRENT_STATE not in(4,5,14,15,22,23) and  b.BUG_NUM  ='"+dto.getUsersId()+"' and CURRENT_HANDLER <> '"+dto.getUsersId()+"' ";
			}

			HashMap<String, Object> map = new HashMap<String, Object>();
			if(dto.getOtherMission() != null){
				if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
					sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"' ";
					queryPkgSql = queryPkgSql + " and tp.taskId='"+dto.getOtherMission().getProjectId()+"'";
					queryItrtSql = queryItrtSql + " and itrt.task_id='"+dto.getOtherMission().getProjectId()+"'";
					queryItrtSql2 = queryItrtSql2 + " and itrt.task_id='"+dto.getOtherMission().getProjectId()+"'";
				}
			}
			String exeSql = "select  base.* from ("+sql+
				     "  union all " + sq2 +") base order by base.update_time,base.create_time desc ";
			List<Map<String,Object>> missions = otherMissionService.commonfindBySqlByJDBC(exeSql, true, map); 
			for(Map<String,Object> taskMap :missions) {
				taskMap.put("dataType", "task");
			}
			List<Map<String,Object>> testPkgList =  otherMissionService.commonfindBySqlByJDBC(queryPkgSql, true, map); 
			for(Map<String,Object> tpkgMap :testPkgList) {
				tpkgMap.put("dataType", "testPkg");
				int exeCount =Integer.parseInt(tpkgMap.get("exeCount").toString());
				int notExeCount =Integer.parseInt(tpkgMap.get("notExeCount").toString());
				if(exeCount>0&&notExeCount==0) {
					tpkgMap.put("status", "2");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount));
				} else if(exeCount>0&&notExeCount>0) {
					tpkgMap.put("status", "1");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount+notExeCount));
				} else if(exeCount==0&&notExeCount>0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "0/"+String.valueOf(notExeCount));
				}  else if(exeCount==0&&notExeCount==0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "无用例");
				} 
			}

			if(!testPkgList.isEmpty()) {
				missions.addAll(0,testPkgList);
			}
			if(queryBugSql!=null) {
				List<Map<String,Object>> bugList =  otherMissionService.commonfindBySqlByJDBC(queryBugSql, true, map); 
				if(!bugList.isEmpty()) {
					for(Map<String,Object> bugMap :bugList) {
						bugMap.put("status", "0");
						if(bugMap.get("bugStatus")!=null) {
							int bugStatus =Integer.parseInt(bugMap.get("bugStatus").toString());
							bugMap.put("bugStatus",BugFlowConst.getStateName(bugStatus));
							
						}
					}
					missions.addAll(bugList);
				}
			}
			String exeItrtSql = "select  base.* from ("+queryItrtSql+
				     "  union all " + queryItrtSql2 +") base order by base.update_time,base.create_time desc";
			List<Map<String,Object>> itrtList =  otherMissionService.commonfindBySqlByJDBC(exeItrtSql, true, map); 
			for(Map<String,Object> itrtMap :itrtList) {
				itrtMap.put("dataType", "itrt");
				if(itrtMap.get("status")==null) {
					itrtMap.put("status", "0");
				}else {
				////0、进行中，1、完成，2、结束，3、准备，5、暂停，6、终止  转换为前端统一的状态 
					int intStatus =Integer.parseInt(itrtMap.get("status").toString());
					if(intStatus==0) {
						itrtMap.put("status", "1");
					} else if(intStatus==1) {
						itrtMap.put("status", "2");
					}else if(intStatus==2||intStatus==6) {
						itrtMap.put("status", "3");
					}  else if(intStatus==3) {
						itrtMap.put("status", "0");
					}else if(intStatus==5) {
						itrtMap.put("status", "4");
					}
				}
			}
			if(!itrtList.isEmpty()) {
				missions.addAll(itrtList);
			}
			if(missions != null && missions.size() > 0){
				super.writeResult(JsonUtil.toJson(missions));
			}else{
				super.writeResult(JsonUtil.toJson(new ArrayList<Map<String,Object>>()));
			}
		}else{
			String currUserId = SecurityContextHolderHelp.getUserId();
			String sql = "select misson.* from "+
						" (SELECT om.* FROM t_other_mission om "+
						" join t_user_other_mission uom on om.mission_id=uom.mission_id join t_single_test_task st on om.project_id= st.taskid and   st.status_flg<>4  where "+
						"  uom.user_id="+ "'"+currUserId+"'"+
						" union "+
						" SELECT om.* FROM t_other_mission om "+
						" join   t_concern_other_mission com on om.mission_id=com.mission_id join t_single_test_task st on om.project_id= st.taskid and   st.status_flg<>4  where "+
						"  com.user_id="+ "'"+currUserId+"'"+
						" union "+
						" SELECT om.* FROM t_other_mission om join t_single_test_task st on om.project_id= st.taskid and  st.status_flg<>4  where om.create_user_id="+ "'"+SecurityContextHolderHelp.getLoginName()+"'"+
						" ) misson where misson.mission_id is not null ";
			
			String sql2 = "select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id where  (om.project_id is null or om.project_id='') and  "+
					"  uom.user_id="+ "'"+currUserId+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id where  (om.project_id is null or om.project_id='') and "+
					"  com.user_id="+ "'"+currUserId+"'"+
					" union "+
					" SELECT om.* FROM t_other_mission om where  (om.project_id is null or om.project_id='') and om.create_user_id="+ "'"+SecurityContextHolderHelp.getLoginName()+"'"+
					" ) misson where misson.mission_id is not null "; 
			String queryPkgSql = "SELECT tp.taskId as project_id,tp.id as mission_id,tp.executor,tp.package_name  as mission_name,IFNULL(tp.exe_count,0) as exe_count,IFNULL(tp.not_exe_count,0) as not_exe_count ,tp.expected_start_time,tp.expected_end_time,"+
			" tp.actual_start_time , st.pro_name as projectName FROM t_testcasepackage tp join   t_user_testcasepkg utpgk "+
			" on tp.id = utpgk.packageId  and utpgk.userId='"+currUserId+"'  join t_single_test_task st on tp.taskId= st.taskid and st.status_flg !=4 " ;
			String queryBugSql = null;
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				queryBugSql = "SELECT b.TASK_ID AS project_id,b.BUGCARDID AS mission_id,b.BUG_NUM  AS next_owner_id ,'"+SecurityContextHolderHelp.getMyRealName()+"' as 'handler_name',b.BUGDESC  as mission_name,b.CURRENT_STATE AS bug_status,'bug' AS 'data_type'   " ;
				queryBugSql = queryBugSql +" FROM t_bugbaseinfo  b WHERE b.task_id = '"+dto.getOtherMission().getProjectId()+"' and  b.CURRENT_STATE not in(4,5,14,15,22,23) and  b.BUG_NUM  ='"+currUserId+"' and CURRENT_HANDLER <> '"+currUserId+"' ";
			}
			HashMap<String, Object> map = new HashMap<String, Object>();
			if(dto.getOtherMission() != null){
				if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
					sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
					sql2 = sql2 + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
					queryPkgSql = queryPkgSql + " and tp.taskId='"+dto.getOtherMission().getProjectId()+"'";
				}else{
					sql = sql + " and misson.create_time > "+ "'"+agoTime+"'";
					queryPkgSql = queryPkgSql + " and tp.create_time > '"+agoTime+"'";
				}
			}
			String exeSql = "select  base.* from ("+sql+
				     "  union all " + sql2 +") base order by base.update_time,base.create_time desc ";
			List<Map<String,Object>> missions = otherMissionService.commonfindBySqlByJDBC(exeSql, true, map); 
			for(Map<String,Object> taskMap :missions) {
				taskMap.put("dataType", "task");
			}
			List<Map<String,Object>> testPkgList =  otherMissionService.commonfindBySqlByJDBC(queryPkgSql, true, map); 
			for(Map<String,Object> tpkgMap :testPkgList) {
				tpkgMap.put("dataType", "testPkg");
				int exeCount =Integer.parseInt(tpkgMap.get("exeCount").toString());
				int notExeCount =Integer.parseInt(tpkgMap.get("notExeCount").toString());
				if(exeCount>0&&notExeCount==0) {
					tpkgMap.put("status", "2");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount));
				} else if(exeCount>0&&notExeCount>0) {
					tpkgMap.put("status", "1");
					tpkgMap.put("completionDegree", String.valueOf(exeCount)+"/"+String.valueOf(exeCount+notExeCount));
				} else if(exeCount==0&&notExeCount>0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "0/"+String.valueOf(notExeCount));
				}  else if(exeCount==0&&notExeCount==0) {
					tpkgMap.put("status", "0");
					tpkgMap.put("completionDegree", "无用例");
				}  
			}
			if(!testPkgList.isEmpty()) {
				missions.addAll(0,testPkgList);
			}   
			if(queryBugSql!=null) {
				List<Map<String,Object>> bugList =  otherMissionService.commonfindBySqlByJDBC(queryBugSql, true, map); 
				if(!bugList.isEmpty()) {
					for(Map<String,Object> bugMap :bugList) {
						bugMap.put("status", "0");
						if(bugMap.get("bugStatus")!=null) {
							int bugStatus =Integer.parseInt(bugMap.get("bugStatus").toString());
							bugMap.put("bugStatus",BugFlowConst.getStateName(bugStatus));
						}
					}
					missions.addAll(bugList);
				}
			}
			List<Map<String,Object>> itrtList = null;
			if(dto.getOtherMission() != null){
				if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
					itrtList = queryCurrUserItrtList(dto.getOtherMission().getProjectId(),agoTime);
				}else {
					itrtList = queryCurrUserItrtList(null,agoTime);
				}
			}else {
				itrtList = queryCurrUserItrtList(null,agoTime);
			}
			if(itrtList!=null&&!itrtList.isEmpty()) {
				missions.addAll(itrtList);
			}
			if(missions != null && missions.size() > 0){
				super.writeResult(JsonUtil.toJson(missions));
			}else{
				super.writeResult(JsonUtil.toJson(new ArrayList<Map<String,Object>>()));
			}
 		}
		 
		return super.globalAjax();
	}
	
	private List<Map<String,Object>> queryCurrUserItrtList(String taskId ,String agoTime) {

		String currUserId  = SecurityContextHolderHelp.getUserId();
		String loginName = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getLoginName();
		HashMap<String,Object> hashMap = new HashMap<String,Object>();
		StringBuffer buffer = new StringBuffer();
		buffer.append(" select  iteration_id as  mission_id ,task_id as project_id ,iteration_bag_name as mission_name ,iteration_status as status ,start_time ,end_time,association_project as projectName  from ( ") ;
		buffer.append(" SELECT it.* FROM t_iteration_list it ,(SELECT TASKID as project_id ,PRO_NAME as project_name FROM t_single_test_task where filter_flag <>1 ");
		buffer.append(" and (CREATE_ID='"+currUserId+"' ");
		buffer.append(" or psm_id='"+currUserId+"' ) and status_flg!=4 ");

		buffer.append( "union "
				+ " SELECT distinct t.taskid as project_id , t.PRO_NAME as project_name "
				+ " FROM t_single_test_task t join t_task_useactor uat on t.taskid=uat.taskid "
				+ " and uat.is_enable=1 and t.status_flg!=4  ");
		buffer.append(" and uat.userid='"+currUserId +"' ");

		
		buffer.append(

				" UNION"+
				" SELECT DISTINCT"+
				" pro.taskid AS projectId,"+
				" pro.pro_name AS projectName"+
				" FROM"+
				" t_single_test_task pro,"+
				" ("+
				" SELECT"+
				" om.*"+
				" FROM"+
				" t_other_mission om"+
				" JOIN t_user_other_mission uom ON om.mission_id = uom.mission_id"+
				" AND om.project_id IS NOT NULL"+
				" AND uom.user_id ='"+currUserId+"' "+
				" UNION"+
				" SELECT"+
				" om.*"+
				" FROM"+
				" t_other_mission om"+
				" JOIN t_concern_other_mission com ON om.mission_id = com.mission_id"+
				" AND om.project_id IS NOT NULL"+
				" AND com.user_id ='"+currUserId+"' "+
				" UNION"+
				" SELECT"+
				" om.*"+
				" FROM"+
				" t_other_mission om"+
				" WHERE"+
				" om.create_user_id ='"+loginName+"' "+
				" AND om.project_id IS NOT NULL"+
				" ) proTask"+
				" WHERE"+
				" pro.taskid = proTask.project_id and pro.status_flg!=4 )");
		buffer.append(" mytask where it.iteration_id is not null ");
		if(StringUtils.isNullOrEmpty(taskId)){
			buffer.append(" and (mytask.project_id = it.task_id or it.user_id='"+currUserId+"') ");
			buffer.append(" and  it.create_time >'"+agoTime +"' ");
		}else {
			buffer.append(" and it.task_id='"+taskId+"' ");
			buffer.append(" and mytask.project_id = it.task_id and mytask.project_id='"+taskId+"' ");
		}
		
		
		//hashMap.put("userId3", currUserId);
		buffer.append(" union SELECT it1.* FROM t_iteration_list  it1  where it1.task_id is null  or it1.task_id =''");
		buffer.append(" and it1.user_id='"+currUserId +"' ");
		//hashMap.put("userId4", currUserId);
		if(StringUtils.isNullOrEmpty(taskId)){
			buffer.append(" and it1.create_time >'"+agoTime +"' ");
		}else {
			buffer.append(" and it1.task_id ='"+taskId +"' ");
			//hashMap.put("task_id", taskId);
		}
		buffer.append(   ")  allMyIt order by allMyIt.create_time desc ");
	
		List<Map<String,Object>> itrtList = otherMissionService.commonfindBySqlByJDBC(buffer.toString(), true, hashMap);
		for(Map<String,Object> itrtMap :itrtList) {
			itrtMap.put("dataType", "itrt");
			if(itrtMap.get("status")==null) {
				itrtMap.put("status", "0");
			}else {
			////0、进行中，1、完成，2、结束，3、准备，5、暂停，6、终止  转换为前端统一的状态 
				int intStatus =Integer.parseInt(itrtMap.get("status").toString());
				if(intStatus==0) {
					itrtMap.put("status", "1");
				} else if(intStatus==1) {
					itrtMap.put("status", "2");
				}else if(intStatus==2||intStatus==6) {
					itrtMap.put("status", "3");
				}  else if(intStatus==3) {
					itrtMap.put("status", "0");
				}else if(intStatus==5) {
					itrtMap.put("status", "4");
				}
			}
		}
		return itrtList;
	}
	
	/** 
	* 方法名:          getAllOtherMissionList
	* 方法功能描述:    得到所有相关的任务（非管理员）
	* @param:        
	* @return:        
	* @Author:          
	* @Create Date:   2018年12月27日 上午11:27:59
	*/
	public View getAllOtherMissionList(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		String userId = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getId();
		String loginName = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getLoginName();
		String sql = "select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id "+
					" and uom.user_id="+ "'"+userId+"'"+
					"  join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4 "
					+ "union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id "+
					" and com.user_id="+ "'"+userId+"'"
					 +"   join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4 " +
					" union "+
					" SELECT om.* FROM t_other_mission om   join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4  where om.create_user_id="+ "'"+loginName+"'"+
					" ) misson where misson.mission_id is not null ";
					
		if(dto.getOtherMission() != null){
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getStatus())&&!"5".equals(dto.getOtherMission().getStatus())){
				sql = sql + " and misson.status ="+ "'"+dto.getOtherMission().getStatus()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getMissionName())){
				sql = sql + " and misson.mission_name like "+ "'%"+dto.getOtherMission().getMissionName()+"%'";
			}
			if("5".equals(dto.getOtherMission().getStatus())){
				sql = sql + "  and  (misson.status ='1' or misson.status ='0') and misson.predict_end_time<=now() " ;
				
			}
		}
		if(!StringUtils.isNullOrEmpty(dto.getOtherChargeId())){
			sql = sql + " and misson.charge_person_id ="+ "'"+dto.getOtherChargeId()+"'";
		}
			

		sql = sql +"  union "
					+"select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id "+
					" and uom.user_id="+ "'"+userId+"'"+
					"  and  (om.project_id is null or om.project_id ='')"
					+ "union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id "+
					" and com.user_id="+ "'"+userId+"'"
					 +"    and  (om.project_id is null or om.project_id ='')" +
					" union "+
					" SELECT om.* FROM t_other_mission om  where  (om.project_id is null or om.project_id ='')  and  om.create_user_id="+ "'"+loginName+"'"+
					" ) misson where misson.mission_id is not null " ;
					
					;
		if(dto.getOtherMission() != null){
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getStatus())&&!"5".equals(dto.getOtherMission().getStatus())){
				sql = sql + " and misson.status ="+ "'"+dto.getOtherMission().getStatus()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getMissionName())){
				sql = sql + " and misson.mission_name like "+ "'%"+dto.getOtherMission().getMissionName()+"%'";
			}
			if("5".equals(dto.getOtherMission().getStatus())){
				sql = sql + "  and  (misson.status ='1' or misson.status ='0') and misson.predict_end_time<=now() " ;
				
			}
		}
		if(!StringUtils.isNullOrEmpty(dto.getOtherChargeId())){
			sql = sql + " and misson.charge_person_id ="+ "'"+dto.getOtherChargeId()+"'";
		}
		//sql = sql +"  order by misson.update_time desc";
		PageModel pageModel = new PageModel();
		sql  = "select myDataTable.* from ("+sql +") myDataTable order by myDataTable.update_time desc ";
		pageModel.setQueryHql(sql);
		pageModel.setPageNo(dto.getPageNo());
		pageModel.setPageSize(dto.getPageSize());
		
		otherMissionService.getJdbcTemplateWrapper().fillPageModelData(pageModel,OtherMission.class, "mission_id");
		setMissionJoiners((List<OtherMission>)pageModel.getRows());
		if(pageModel.getRows() == null){
			pageModel.setRows(new ArrayList<OtherMission>());
		}
		super.writeResult(JsonUtil.toJson(pageModel));
		return super.globalAjax();
	}
	
	
	private void setMissionJoiners(List<OtherMission> otherMissions) {
		if(otherMissions==null||otherMissions.isEmpty()) {
			return;
		}
		String hql  = " from UserOtherMission uom where uom.missionId in(:ids) ";
		List<String> missionIds = new ArrayList<String>();
		if(otherMissions != null &&!otherMissions.isEmpty()) {
			for(OtherMission otherMission :otherMissions) {
				missionIds.add(otherMission.getMissionId());
			}
		}
		Map praValuesMap = new HashMap(1);
		praValuesMap.put("ids", missionIds);
		List<UserOtherMission> userOtherMissions = otherMissionService.findByHqlWithValuesMap(hql, praValuesMap, false);
		if(userOtherMissions!=null&&!userOtherMissions.isEmpty()) {
			Map<String, List<String>> missionJoinerMap = new HashMap<String, List<String>>();
			for(int i=0;i<missionIds.size();i++){
				List<String> userIds = new ArrayList<String>();
				for(int t=0;t<userOtherMissions.size();t++){
					if(missionIds.get(i).equals(userOtherMissions.get(t).getMissionId())){
						userIds.add(userOtherMissions.get(t).getUserId());
					}
				}
				missionJoinerMap.put(missionIds.get(i), userIds);
			}
			String hql1 = "select new cn.com.codes.object.User(u.id,u.name) from User u where u.id in (select uom.userId from UserOtherMission uom where uom.missionId in(:missionIds) ) ";
			Map praValuesMap1 = new HashMap(1);
			praValuesMap1.put("missionIds", missionIds);
			List<User> users = otherMissionService.findByHqlWithValuesMap(hql1, praValuesMap1, false);
			if(users != null && users.size() > 0){
				for(int y=0;y<otherMissions.size();y++){
					List<String> userIds = missionJoinerMap.get(otherMissions.get(y).getMissionId());
					List<String> userNames = new ArrayList<String>();
					for(int p=0;p<userIds.size();p++){
						for(int q=0;q<users.size();q++){
							if(userIds.get(p).equals(users.get(q).getId())){
								userNames.add(users.get(q).getName());
							}
						}
					}
					otherMissions.get(y).setMissionJoiners(listToString1(userNames,","));
				}
			}
		}
	}
	
	private void setProjectName(List<OtherMission> otherMissions) {
		if(otherMissions==null||otherMissions.isEmpty()) {
			return;
		}
		String hql  = " from SingleTestTask stt where stt.taskId in(:taskIds) ";
		List<String> taskIds = new ArrayList<String>();
		taskIds.add("1234321");
		if(otherMissions != null &&!otherMissions.isEmpty()) {
			for(OtherMission otherMission :otherMissions) {
				if(!StringUtils.isNullOrEmpty(otherMission.getProjectId())){
					taskIds.add(otherMission.getProjectId());
				}
			}
		}
		Map praValuesMap = new HashMap(1);
		praValuesMap.put("taskIds", taskIds);
		List<SingleTestTask> singleTestTasks = otherMissionService.findByHqlWithValuesMap(hql, praValuesMap, false);
		if(singleTestTasks!=null&&!singleTestTasks.isEmpty()) {
			Map<String, String> singleTestTasksMap = new HashMap<String, String>();
			for(int i=0;i<singleTestTasks.size();i++){
				singleTestTasksMap.put(singleTestTasks.get(i).getTaskId(), singleTestTasks.get(i).getProName());
			}
			for(OtherMission otherMission :otherMissions) {
				if(!StringUtils.isNullOrEmpty(otherMission.getProjectId())){
					otherMission.setProjectId(singleTestTasksMap.get(otherMission.getProjectId()));
				}
			}
		}
	}
	
	private void setChargePeopleName(List<OtherMission> otherMissions) {
		if(otherMissions==null||otherMissions.isEmpty()) {
			return;
		}
		String hql  = " from User u where u.id in(:ids) ";
		List<String> ids = new ArrayList<String>();
		ids.add("1234321");
		if(otherMissions != null &&!otherMissions.isEmpty()) {
			for(OtherMission otherMission :otherMissions) {
				if(!StringUtils.isNullOrEmpty(otherMission.getChargePersonId())){
					ids.add(otherMission.getChargePersonId());
				}
			}
		}
		Map praValuesMap = new HashMap(1);
		praValuesMap.put("ids", ids);
		List<User> users = otherMissionService.findByHqlWithValuesMap(hql, praValuesMap, false);
		if(users!=null&&!users.isEmpty()) {
			Map<String, String> usersMap = new HashMap<String, String>();
			for(int i=0;i<users.size();i++){
				usersMap.put(users.get(i).getId(), users.get(i).getName());
			}
			for(OtherMission otherMission :otherMissions) {
				if(!StringUtils.isNullOrEmpty(otherMission.getChargePersonId())){
					otherMission.setChargePersonId(usersMap.get(otherMission.getChargePersonId()));
				}
			}
		}
	}
	
	/** 
	* 方法名:          exportMissions
	* 方法功能描述:    
	* @param:        
	* @return:        
	* @Author:          
	*/
	@SuppressWarnings("unchecked")
	public View exportMissions(BusiRequestEvent req) {
		final OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		PageModel pg = new PageModel();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		if(!StringUtils.isNullOrEmpty(dto.getIsNotManagerSearchAll()) && dto.getIsNotManagerSearchAll().equals("1")){
			pg = getAllOtherMissionListNotManager(dto);
		}else{
			this.buildOtherMissionListSql(dto);
			pg.setPageNo(1);
			pg.setPageSize(2000);
			pg.setHqlParamMap(dto.getHqlParamMaps());
			pg.setQueryHql(dto.getHql());
			
			otherMissionService.getJdbcTemplateWrapper().fillPageModelData(pg, OtherMission.class, "");
			setMissionJoiners((List<OtherMission>)pg.getRows());
			setProjectName((List<OtherMission>)pg.getRows());
			setChargePeopleName((List<OtherMission>)pg.getRows());
		}
		
		XSSFWorkbook wb = new XSSFWorkbook();
		String path = ServletActionContext.getServletContext().getRealPath(File.separator);

		XSSFSheet sheet = wb.createSheet("sheet1");
		// 设置栏目中文名称
		XSSFRow zhNameRow = sheet.createRow(0);
		XSSFCell zhNameCell0 = zhNameRow.createCell(0);
		XSSFCell zhNameCell1 = zhNameRow.createCell(1);
		XSSFCell zhNameCell2 = zhNameRow.createCell(2);
		XSSFCell zhNameCell3 = zhNameRow.createCell(3);
		XSSFCell zhNameCell4 = zhNameRow.createCell(4);
		XSSFCell zhNameCell5 = zhNameRow.createCell(5);
		XSSFCell zhNameCell6 = zhNameRow.createCell(6);
		XSSFCell zhNameCell7 = zhNameRow.createCell(7);
		XSSFCell zhNameCell8 = zhNameRow.createCell(8);
		XSSFCell zhNameCell9 = zhNameRow.createCell(9);
		zhNameCell0.setCellValue("任务名称");
		zhNameCell1.setCellValue("所属项目");
		zhNameCell2.setCellValue("任务参与者");
		zhNameCell3.setCellValue("任务负责人");
		zhNameCell4.setCellValue("实际工作量(小时)");
		zhNameCell5.setCellValue("任务描述");
		zhNameCell6.setCellValue("任务进度(%)");
		zhNameCell7.setCellValue("状态");
		zhNameCell8.setCellValue("预计开始时间");
		zhNameCell9.setCellValue("实际开始时间");
		if(pg.getTotal() > 0){
			// 将数据从list写到Excel文档
			for (int i = 0; i < ((List<OtherMission>)pg.getRows()).size(); i++) {
				XSSFRow row = sheet.createRow(i + 1);
				OtherMission otherMission = ((List<OtherMission>)pg.getRows()).get(i);
				
				XSSFCell missionNameCell = row.createCell(0);
				XSSFCell projectCell = row.createCell(1);
				XSSFCell joinerCell = row.createCell(2);
				XSSFCell chargePeopleCell = row.createCell(3);
				XSSFCell actuallyCell = row.createCell(4);
				XSSFCell descCell = row.createCell(5);
				XSSFCell degreeCell = row.createCell(6);
				XSSFCell statusCell = row.createCell(7);
				XSSFCell predictStartTimeCell = row.createCell(8);
				XSSFCell actuallyStartTimeCell = row.createCell(9);
				
				missionNameCell.setCellValue(otherMission.getMissionName());
				projectCell.setCellValue(otherMission.getProjectId());
				joinerCell.setCellValue(otherMission.getMissionJoiners());
				chargePeopleCell.setCellValue(otherMission.getChargePersonId());
				actuallyCell.setCellValue(otherMission.getActualWorkload());
				descCell.setCellValue(otherMission.getDescription());
				degreeCell.setCellValue(otherMission.getCompletionDegree());
				if(otherMission.getStatus().equals("0")){
					statusCell.setCellValue("未开始");
				}else if(otherMission.getStatus().equals("1")){
					statusCell.setCellValue("进行中");
				}else if(otherMission.getStatus().equals("2")){
					statusCell.setCellValue("完成");
				}else if(otherMission.getStatus().equals("3")){
					statusCell.setCellValue("终止");
				}else if(otherMission.getStatus().equals("4")){
					statusCell.setCellValue("暂停");
				}
				if(otherMission.getPredictStartTime() != null){
					predictStartTimeCell.setCellValue(formatter.format(otherMission.getPredictStartTime()));
				}
				if(otherMission.getRealStartTime() != null){
					actuallyStartTimeCell.setCellValue(formatter.format(otherMission.getRealStartTime()));
				}
			}
		}
		
		FileOutputStream exportExcelFile = null;
		// 将Excel工作簿存盘
		String time = String.valueOf((new Date()).getTime());
		int userInt = SecurityContextHolderHelp.getUserId().hashCode();
		try {
			// 输出文件
			exportExcelFile = new FileOutputStream(path + File.separator
					+ "mypmUserFiles" + File.separator + "expFile"
					+ File.separator + "missionsExport_" + time + userInt+".xlsx");
			wb.write(exportExcelFile);
		} catch (IOException e) {
			logger.error(e);
		} finally {
			if (exportExcelFile != null) {
				try {
					exportExcelFile.flush();
					exportExcelFile.close();
				} catch (IOException e) {
					logger.error(e);
				}
			}
		}
		
		InputStream exportStuStream = getExpImpFileInputStream(time,userInt);
		UniversalView view = new UniversalView();
		view.displayData("excel", exportStuStream);
		File file=new File(path + File.separator
				+ "mypmUserFiles" + File.separator + "expFile"
				+ File.separator + "missionsExport_" + time +userInt+ ".xlsx"
						+ "");
		//导出的输入流己写，删除磁盘上的导出文件
		try {
			exportStuStream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(file.exists()&&file.isFile()) {
			file.delete();
		}
		return view;
	}
	
	private PageModel getAllOtherMissionListNotManager(OtherMissionDto dto){
		String userId = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getId();
		String loginName = SecurityContextHolder.getContext().getVisit().getUserInfo(VisitUser.class).getLoginName();
		String sql = "select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id "+
					" and uom.user_id="+ "'"+userId+"'"+
					"  join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4 "
					+ "union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id "+
					" and com.user_id="+ "'"+userId+"'"
					 +"   join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4 " +
					" union "+
					" SELECT om.* FROM t_other_mission om   join t_single_test_task st on om.project_id= st.taskid and st.status_flg !=4  where om.create_user_id="+ "'"+loginName+"'"+
					" ) misson where misson.mission_id is not null ";
					
		if(dto.getOtherMission() != null){
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getStatus())&&!"5".equals(dto.getOtherMission().getStatus())){
				sql = sql + " and misson.status ="+ "'"+dto.getOtherMission().getStatus()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getMissionName())){
				sql = sql + " and misson.mission_name like "+ "'%"+dto.getOtherMission().getMissionName()+"%'";
			}
			if("5".equals(dto.getOtherMission().getStatus())){
				sql = sql + "  and  (misson.status ='1' or misson.status ='0') and misson.predict_end_time<=now() " ;
				
			}
		}
		if(!StringUtils.isNullOrEmpty(dto.getOtherChargeId())){
			sql = sql + " and misson.charge_person_id ="+ "'"+dto.getOtherChargeId()+"'";
		}
			
		sql = sql +"  union "
					+"select misson.* from "+
					" (SELECT om.* FROM t_other_mission om "+
					" join t_user_other_mission uom on om.mission_id=uom.mission_id "+
					" and uom.user_id="+ "'"+userId+"'"+
					"  and  (om.project_id is null or om.project_id ='')"
					+ "union "+
					" SELECT om.* FROM t_other_mission om "+
					" join   t_concern_other_mission com on om.mission_id=com.mission_id "+
					" and com.user_id="+ "'"+userId+"'"
					 +"    and  (om.project_id is null or om.project_id ='')" +
					" union "+
					" SELECT om.* FROM t_other_mission om  where  (om.project_id is null or om.project_id ='')  and  om.create_user_id="+ "'"+loginName+"'"+
					" ) misson where misson.mission_id is not null " ;
					
					;
		if(dto.getOtherMission() != null){
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getProjectId())){
				sql = sql + " and misson.project_id ="+ "'"+dto.getOtherMission().getProjectId()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getStatus())&&!"5".equals(dto.getOtherMission().getStatus())){
				sql = sql + " and misson.status ="+ "'"+dto.getOtherMission().getStatus()+"'";
			}
			if(!StringUtils.isNullOrEmpty(dto.getOtherMission().getMissionName())){
				sql = sql + " and misson.mission_name like "+ "'%"+dto.getOtherMission().getMissionName()+"%'";
			}
			if("5".equals(dto.getOtherMission().getStatus())){
				sql = sql + "  and  (misson.status ='1' or misson.status ='0') and misson.predict_end_time<=now() " ;
				
			}
		}
		if(!StringUtils.isNullOrEmpty(dto.getOtherChargeId())){
			sql = sql + " and misson.charge_person_id ="+ "'"+dto.getOtherChargeId()+"'";
		}
		sql  = "select myDataTable.* from ("+sql +") myDataTable order by myDataTable.update_time desc ";
		PageModel pageModel = new PageModel();
		pageModel.setQueryHql(sql);
		pageModel.setPageNo(1);
		pageModel.setPageSize(2000);
		
		otherMissionService.getJdbcTemplateWrapper().fillPageModelData(pageModel,OtherMission.class, "mission_id");
		setMissionJoiners((List<OtherMission>)pageModel.getRows());
		setProjectName((List<OtherMission>)pageModel.getRows());
		setChargePeopleName((List<OtherMission>)pageModel.getRows());
		return pageModel;
	}
	
	private InputStream getExpImpFileInputStream(String time,int userInt) {

		InputStream studentInfoStream = ServletActionContext
				.getServletContext().getResourceAsStream(
						File.separator + "mypmUserFiles" + File.separator
								+ "expFile" + File.separator + "missionsExport_"
								+ time + userInt+".xlsx");
		return studentInfoStream;
	}
	
	public static String listToString1(List<String> list, String separator) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < list.size(); i++) {
            sb.append(list.get(i)).append(separator);
        }
        return list.isEmpty() ? "" : sb.toString().substring(0, sb.toString().length() - 1);
    }
	
	/** 
	* 方法名:          getFileInfoByMissionId
	* 方法功能描述:    通过mission查询文件信息
	* @param:         
	* @return:        
	* @Author:        
	* @Create Date:   2018年10月11日 上午9:32:05
	*/
	public View getFileInfoByMissioId(BusiRequestEvent req){
		OtherMissionDto dto = super.getDto(OtherMissionDto.class, req);
		if(dto.getFileInfo()!=null){
			FileInfoDto fileInfoDto = new FileInfoDto();
			fileInfoDto.setFileInfo(dto.getFileInfo());
			List<FileInfo> fileInfos = fileInfoService.getFileInfoByMissionId(fileInfoDto);
			super.writeResult(JsonUtil.toJson(fileInfos));
			
		}
		return super.globalAjax();
	}
	
	public OtherMissionService getOtherMissionService() {
		return otherMissionService;
	}


	public void setOtherMissionService(OtherMissionService otherMissionService) {
		this.otherMissionService = otherMissionService;
	}

	public FileInfoService getFileInfoService() {
		return fileInfoService;
	}

	public void setFileInfoService(FileInfoService fileInfoService) {
		this.fileInfoService = fileInfoService;
	}
}
